--==============================================
--Author Panny
--date   2009/4/3
--Dec   Get New Order-Number Key 
--===============================================
CREATE FUNCTION dbo.fn_GetOrderNumber (
)
RETURNS char(9)
AS
BEGIN
     DECLARE @OrderIDShow char(9),@SerialNumber int,@Serial char(3)
     SELECT @SerialNumber = COUNT(*) FROM [TT_Orders] WHERE CONVERT(varchar(8),CreateOn,112) = CONVERT(varchar(8),GETDATE(),112)
     IF (@SerialNumber <= 9)
     BEGIN
         SET @Serial = '000'+CONVERT(CHAR(1),@SerialNumber)
     END
     ELSE IF (@SerialNumber > 9 AND @SerialNumber <= 99)
     BEGIN
         SET @Serial = '00'+CONVERT(CHAR(2),@SerialNumber)
     END
     ELSE IF (@SerialNumber > 99 AND @SerialNumber <= 999)
     BEGIN
         SET @Serial ='0'+ CONVERT(CHAR(3),@SerialNumber)
     END
     ELSE IF (@SerialNumber > 999 AND @SerialNumber <= 9999)
     BEGIN
         SET @Serial =CONVERT(CHAR(3),@SerialNumber)
     END

     SET @OrderIDShow = CONVERT(varchar(6),GETDATE(),12)+@Serial
     
     RETURN @OrderIDShow
END